lyfz_repos/铂金版源码/DBFile/SQL/2015-08-13[收集]/2015-08-22DB.txt

--数码表
if not exists
(select * from syscolumns where id=object_id('tb_ErpOrderDigital') and name='Ordv_SendPhotoName')
begin
	alter table tb_ErpOrderDigital add Ordv_SendPhotoName  nvarchar(20) NULL
end
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'tb_ErpOrderDigital', N'COLUMN',N'Ordv_SendPhotoName'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发片人（按最后一个发片人）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpOrderDigital', @level2type=N'COLUMN',@level2name=N'Ordv_SendPhotoName'
GO
if not exists
(select * from syscolumns where id=object_id('tb_ErpOrderDigital') and name='Ordv_SendPhotoTime')
begin
	alter table tb_ErpOrderDigital add Ordv_SendPhotoTime  datetime
end
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'tb_ErpOrderDigital', N'COLUMN',N'Ordv_SendPhotoTime'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发片时间（按最后一个发片时间）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpOrderDigital', @level2type=N'COLUMN',@level2name=N'Ordv_SendPhotoTime'
GO
if not exists
(select * from syscolumns where id=object_id('tb_ErpOrderDigital') and name='Ordv_SendPhotoDispatcher')
begin
	alter table tb_ErpOrderDigital add Ordv_SendPhotoDispatcher  nvarchar(20) NULL
end
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'tb_ErpOrderDigital', N'COLUMN',N'Ordv_SendPhotoDispatcher'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发片安排者' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpOrderDigital', @level2type=N'COLUMN',@level2name=N'Ordv_SendPhotoDispatcher'
GO
if not exists
(select * from syscolumns where id=object_id('tb_ErpOrderDigital') and name='Ordv_SendPhotoDispatchTime')
begin
	alter table tb_ErpOrderDigital add Ordv_SendPhotoDispatchTime  nvarchar(20) NULL
end
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'tb_ErpOrderDigital', N'COLUMN',N'Ordv_SendPhotoDispatchTime'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发片安排时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpOrderDigital', @level2type=N'COLUMN',@level2name=N'Ordv_SendPhotoDispatchTime'
GO

-------- 我的工作_发片师
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_MyWorkSendPhoto_No')
BEGIN
	DROP VIEW [dbo].BView_MyWorkSendPhoto_No
END 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_MyWorkSendPhoto_No]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BView_MyWorkSendPhoto_No]
AS
SELECT   
tb_ErpOrder.ID, CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
dbo.tb_ErpUser.User_DividedShop AS 分店编号, dbo.tb_ErpUser.User_Name AS 员工姓名, 
dbo.tb_ErpOrderDigital.Ordv_ReservationSendPhotoName AS 员工编号, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_ReservationSendPhotoTime)) AS 工作时间,
dbo.tb_ErpOrderDigital.Ordv_ReservationSendPhotoTime AS 工作时间查询
FROM      dbo.tb_ErpUser LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON 
dbo.tb_ErpOrderDigital.Ordv_ReservationSendPhotoName = dbo.tb_ErpUser.User_EmployeeID LEFT OUTER JOIN
dbo.tb_ErpOrder ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrderDigital.Ordv_ReservationSendPhotoStatus IN (''0'', ''1''))
' 
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_MyWorkSendPhoto_Ok')
BEGIN
	DROP VIEW [dbo].BView_MyWorkSendPhoto_Ok
END 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_MyWorkSendPhoto_Ok]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[BView_MyWorkSendPhoto_Ok]
AS
SELECT   
tb_ErpOrder.ID, CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
dbo.tb_ErpUser.User_DividedShop AS 分店编号, dbo.tb_ErpUser.User_Name AS 员工姓名, 
dbo.tb_ErpOrderDigital.Ordv_SendPhotoName AS 员工编号, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_SendPhotoTime)) AS 工作时间,
dbo.tb_ErpOrderDigital.Ordv_SendPhotoTime AS 工作时间查询
FROM      dbo.tb_ErpUser LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON 
dbo.tb_ErpOrderDigital.Ordv_SendPhotoName = dbo.tb_ErpUser.User_EmployeeID LEFT OUTER JOIN
dbo.tb_ErpOrder ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrderDigital.Ordv_ReservationSendPhotoStatus = ''2'')
' 
GO



-------------------2015-08-21------------------------
--打印设置（字体大小）
if not exists
(select * from syscolumns where id=object_id('tb_ErpPrinterSet') and name='ps_FontSize')
begin
	alter table tb_ErpPrinterSet add ps_FontSize  nvarchar(50) NULL
end
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'tb_ErpPrinterSet', N'COLUMN',N'ps_FontSize'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'字体大小' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpPrinterSet', @level2type=N'COLUMN',@level2name=N'ps_FontSize'
GO
--影楼微分享帐号
if not exists
(select * from syscolumns where id=object_id('tb_ErpCompanyInfo') and name='Company_MicroShareAccount')
begin
	alter table tb_ErpCompanyInfo add Company_MicroShareAccount  nvarchar(200) NULL
end
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'tb_ErpCompanyInfo', N'COLUMN',N'Company_MicroShareAccount'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'微分享帐号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCompanyInfo', @level2type=N'COLUMN',@level2name=N'Company_MicroShareAccount'
GO
--影楼微分享密码
if not exists
(select * from syscolumns where id=object_id('tb_ErpCompanyInfo') and name='Company_MicroSharePassword')
begin
	alter table tb_ErpCompanyInfo add Company_MicroSharePassword  nvarchar(200) NULL
end
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'tb_ErpCompanyInfo', N'COLUMN',N'Company_MicroSharePassword'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'微分享密码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpCompanyInfo', @level2type=N'COLUMN',@level2name=N'Company_MicroSharePassword'
GO